SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create Procedure [dbo].[TvShow_Check_FileTitle2]
AS
Declare @EpisodeFile varchar(50), @SeriesId int, @SeasonId INT, @EpisodeId INT,
		@CurrentEpisode INT, @LastEpisode INT
SET @EpisodeFile = 'Breaking Bad - 03x05 - Pilot'
SET @SeriesId = (Select Id From Tv Where Title = 'Breaking Bad')
SET @SeasonId = (Select SeasonId From TvShow_Seasons Where TvId = @SeriesId AND Season = 3)
SET @CurrentEpisode = (SELECT MIN(Id) FROM TvShow_Episodes Where SeasonId = @SeasonId) -- Sets the first episode in the season
SET @LastEpisode = (SELECT MAX(Id) FROM TvShow_Episodes Where SeasonId = @SeasonId) -- Checks for the max episode in the season
--Loops through each episode in the season
WHILE (@CurrentEpisode <= @LastEpisode)
BEGIN
SET @EpisodeId = @CurrentEpisode -- sets the episode if to the current one its searching
	DECLARE @ShowName varchar(100), @SeasonNumber varchar(100), 
			@EpisodeNumber varchar(100), @TitleName varchar(100), 
			@CombinedName varchar(250)
	SET @ShowName = (Select Title From Tv Where Id = @SeriesId)
	SET @SeasonNumber = (Select Season From TvShow_Seasons Where SeasonId = @SeasonId)
	SET @EpisodeNumber = (Select EpisodeId From TvShow_Episodes Where Id = @EpisodeId)
	SET @TitleName = (Select Title From TvShow_Episodes Where Id = @EpisodeId)
	SET @CombinedName = '%' --Starts the beginning of the like--
	DECLARE @count INT, @Amount INT, @Items VARCHAR(1000)
	SET @count = 1
	SET @Amount = (select Count([Type]) From TvShow_FileMatchTypes)
	
	--Loops through and creates a string to match the episode filename
	WHILE (@count <= @Amount)
	BEGIN
		Set @Items = (select Type From TvShow_FileMatchTypes Where Id = @Count)
		SET @count = (@count + 1)
		DECLARE @Item VARCHAR(50)
		DECLARE @Pos INT
		DECLARE @Loop BIT
		SELECT @Loop = CASE WHEN LEN(@Items) > 0 THEN 1 ELSE 0 END
		WHILE (SELECT @Loop) = 1
		BEGIN
		SELECT @Pos = CHARINDEX('+', @Items, 1)
		IF @Pos > 0
			BEGIN
				SELECT @Item = SUBSTRING(@Items, 1, @Pos - 1)
				SELECT @Items = SUBSTRING(@Items, @Pos + 1, LEN(@Items) - @Pos)
				IF (SELECT @Item) = 'ShowTitle '
				Begin
					SET @CombinedName = (@CombinedName + '' + @ShowName)
				End
				IF (SELECT @Item) = ' dash '
				Begin
					SET @CombinedName = (@CombinedName + ' ' + '-')
				End
				IF (SELECT @Item) = ' dashSpace '
				Begin
					SET @CombinedName = (@CombinedName + ' ' + '- ')
				End
				IF (SELECT @Item) = ' SeasonLetter '
				Begin
					SET @CombinedName = (@CombinedName + ' ' + 'S')
				End
				IF (SELECT @Item) = ' SeasonLetterNoSpace '
				Begin
					SET @CombinedName = (@CombinedName + 'S')
				End
				IF (SELECT @Item) = ' EpisodeLetter '
				Begin
					SET @CombinedName = (@CombinedName + ' ' + 'E')
				End
				IF (SELECT @Item) = ' EpisodeLetterNoSpace '
				Begin
					SET @CombinedName = (@CombinedName + 'E')
				End
				IF (SELECT @Item) = ' xNoSpace '
				Begin
					SET @CombinedName = (@CombinedName + 'x')
				End
				IF (SELECT @Item) = ' Season '
				Begin
					SET @CombinedName = (@CombinedName + ' ' + @SeasonNumber)
				End
				IF (SELECT @Item) = ' SeasonNoSpace '
				Begin
					SET @CombinedName = (@CombinedName + @SeasonNumber)
				End
				IF (SELECT @Item) = ' dot '
				Begin
					SET @CombinedName = (@CombinedName + ' ' + '.')
				End
				IF (SELECT @Item) = ' dotNoSpace '
				Begin
					SET @CombinedName = (@CombinedName + '.')
				End
				IF (@EpisodeNumber < 10)
				BEGIN
					IF (SELECT @Item) = ' EpisodeZero '
					Begin
						SET @CombinedName = (@CombinedName + '0')
					End
				END
				IF (@SeasonNumber < 10)
				BEGIN
					IF (SELECT @Item) = ' SeasonZero '
					Begin
						SET @CombinedName = (@CombinedName + '0')
					End
				END
				IF (SELECT @Item) = ' Episode '
				Begin
					SET @CombinedName = (@CombinedName + @EpisodeNumber)
				End
				IF (SELECT @Item) = ' EpisodeTitle '
				Begin
					SET @CombinedName = (@CombinedName + ' ' + @TitleName)
				End
				IF (SELECT @Item) = ' EpisodeTitleNoSpace '
				Begin
					SET @CombinedName = (@CombinedName + @TitleName)
				End
			END
			ELSE
			BEGIN
				SELECT @Item = @Items
				SELECT @Loop = 0
			END
		END
		SET @CombinedName = (@CombinedName + '%')
		IF (@EpisodeFile) LIKE @CombinedName
		Begin
			SELECT EpisodeId From Tvshow_Episodes Where Id = @CurrentEpisode
			SELECT 'TRUE'
		END
		SET @CombinedName = '%'
	END
	SET @CurrentEpisode = @CurrentEpisode + 1
END
GO
